package org.hgvc.webapp.ets.spring.dao;

import java.util.Calendar;
import java.util.Date;
import java.util.List;

import org.hgvc.webapp.ets.bo.CalendarCategory;
import org.hgvc.webapp.ets.bo.CalendarCategoryDtl;
import org.hgvc.webapp.ets.bo.CalendarDtl;
import org.hgvc.webapp.ets.bo.Member;
import org.hgvc.webapp.ets.bo.Partner;
import org.hgvc.webapp.ets.bo.PartnerDocument;
import org.hgvc.webapp.ets.bo.PartnerDocumentCheckList;
import org.hgvc.webapp.ets.bo.PartnerProduct;
import org.hgvc.webapp.ets.bo.PartnerProductBooking;
import org.hgvc.webapp.ets.bo.PartnerProductExtension;
import org.hgvc.webapp.ets.bo.PartnerProductType;
import org.hgvc.webapp.ets.constant.CommonConstant;
import org.hgvc.webapp.ets.constant.PartnerConstant;
import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.criterion.Restrictions;
import org.jocsb.base.AbstractHibernateDao;
import org.jocsb.base.util.DateUtil;


public class PartnerHbmDao extends AbstractHibernateDao {

	public Partner getPartnerByUserId(String userId)throws Exception{
		StringBuffer strb = new StringBuffer();
		strb.append(" select partner from Partner partner where partner.refUser.userId=:userId");
		
		Query query = getSession().createQuery(strb.toString());
		query.setParameter("userId", userId);
		
		return (Partner)query.uniqueResult();
	}
	 
	public List<CalendarCategoryDtl> getCalendarCategoryDtls(String calendarCatId)throws Exception{
		StringBuffer strb = new StringBuffer();
		strb.append(" select c from CalendarCategoryDtl c where ");
		strb.append("   c.calCat.calendarCatId=:calendarCatId and c.isDeleted=:isDeleted");
		
		Query query = getSession().createQuery(strb.toString());
		query.setParameter("calendarCatId", calendarCatId);
		query.setParameter("isDeleted", CommonConstant.NOT_DELETED);
		
		return (List<CalendarCategoryDtl>)query.list();
	}
	public List<Partner> getPartnerByCode(String code)throws Exception{
		StringBuffer strb = new StringBuffer();
		strb.append(" select partner from Partner partner where partner.code=:code and partner.isDeleted=:value");
		
		Query query = getSession().createQuery(strb.toString());
		query.setParameter("code", code);
		query.setParameter("value", CommonConstant.NOT_DELETED);
		return (List<Partner> )query.list();
	}
	
	
	public List<PartnerDocument> getPatnerDocumentListByNameOrCode(String name,String code)throws Exception{
		StringBuffer str = new StringBuffer();
		str.append(" select doc from PartnerDocument doc where (doc.code=:code or doc.name=:name) and doc.isDeleted=:value  ");
		
		Query query = getSession().createQuery(str.toString());
		query.setParameter("name", name);
		query.setParameter("code", code);
		query.setParameter("value", CommonConstant.NOT_DELETED);
		
		return (List<PartnerDocument>)query.list();
	}
	
	public List<PartnerDocument> getPatnerDocumentList()throws Exception{
		StringBuffer str = new StringBuffer();
		str.append(" select doc from PartnerDocument doc where doc.isDeleted=:value  ");
		
		Query query = getSession().createQuery(str.toString());
		query.setParameter("value", CommonConstant.NOT_DELETED);
		
		return (List<PartnerDocument>)query.list();
	}
	
	public List<Partner> getPartnerListWithStatus(List<String> statusList,String searchByCode,String searchByName)throws Exception{
		if (searchByCode==null) searchByCode="";
		if (searchByName==null) searchByName="";
		
		
		StringBuffer str = new StringBuffer();
		//str.append(" select partner from Partner partner where partner.isDeleted=:value  ");
		str.append(" select partner from Partner partner where  ");
		if (!searchByCode.equalsIgnoreCase("")){
			str.append(" partner.code like :code ");
			str.append(" and ");
		}
		
		if (!searchByName.equalsIgnoreCase("")){
			str.append(" partner.compName like :compName ");
			str.append(" and ");
		}
		int i=0;
		for(String status:statusList){
			if (i==0)
				str.append(" (partner.status= '"+status+"'");
			else
				str.append(" or partner.status= '"+status+"'");
			i++;
		}
		str.append(") ");
		
		//System.out.println("searchByCode="+searchByCode);
		//System.out.println("searchByName="+searchByName);
		//System.out.println("sql="+str.toString());
		Query query = getSession().createQuery(str.toString());
		//query.setParameter("value", CommonConstant.NOT_DELETED);
		if (!searchByCode.equalsIgnoreCase("")){
			query.setParameter("code", "%"+searchByCode+"%");
		}
		if (!searchByName.equalsIgnoreCase("")){
			query.setParameter("compName", "%"+searchByName+"%");
		}
		return (List<Partner>)query.list();
		
	}
	
	public List<PartnerDocumentCheckList> getPartnerDocumentCheckListWithType(String type,String partnerId)throws Exception{
		
		
		StringBuffer str = new StringBuffer();
		str.append(" select checklist from PartnerDocumentCheckList checklist where checklist.isDeleted=:value  ");
		str.append(" and checklist.partner.partnerId=:partnerId and checklist.document.type=:type ");
		
		Query query = getSession().createQuery(str.toString());
		query.setParameter("value", CommonConstant.NOT_DELETED);
		query.setParameter("type", type);
		query.setParameter("partnerId", partnerId);
		
		return (List<PartnerDocumentCheckList>)query.list();
	}
	
	public List<PartnerProductType> getPartnerProductTypeListByNameOrCode(String name,String code)throws Exception{
		StringBuffer str = new StringBuffer();
		str.append(" select type from PartnerProductType type where (type.code=:code or type.name=:name) and type.isDeleted=:value  ");
		
		Query query = getSession().createQuery(str.toString());
		query.setParameter("name", name);
		query.setParameter("code", code);
		query.setParameter("value", CommonConstant.NOT_DELETED);
		
		return (List<PartnerProductType>)query.list();
	}
	
	public List<PartnerProductType> getPartnerProductTypeList()throws Exception{
		StringBuffer str = new StringBuffer();
		str.append(" select type from PartnerProductType type where type.isDeleted=:value  ");
		
		Query query = getSession().createQuery(str.toString());
		query.setParameter("value", CommonConstant.NOT_DELETED);
		
		return (List<PartnerProductType>)query.list();
	}
	
	public List<CalendarCategory> getCalendarCategoryList()throws Exception{
		StringBuffer str = new StringBuffer();
		str.append(" select cat from CalendarCategory cat where cat.isDeleted=:value  ");
		
		Query query = getSession().createQuery(str.toString());
		query.setParameter("value", CommonConstant.NOT_DELETED);
		
		return (List<CalendarCategory>)query.list();
	}
	
	public List<CalendarCategory> getCalendarCategoryListByNameOrCode(String name,String code)throws Exception{
		StringBuffer str = new StringBuffer();
		str.append(" select cat from CalendarCategory cat where (cat.code=:code or cat.name=:name) and cat.isDeleted=:value  ");
		
		Query query = getSession().createQuery(str.toString());
		query.setParameter("name", name);
		query.setParameter("code", code);
		query.setParameter("value", CommonConstant.NOT_DELETED);
		
		return (List<CalendarCategory>)query.list();
	}
	
	public List<CalendarDtl> getCalendarDtlListByCatId(String catId)throws Exception{
		StringBuffer str = new StringBuffer();
		str.append(" select cat from CalendarDtl cat where cat.isDeleted=:value  ");
		str.append(" and cat.calCat.calendarCatId=:catId ");
		
		Query query = getSession().createQuery(str.toString());
		query.setParameter("catId", catId);
		query.setParameter("value", CommonConstant.NOT_DELETED);
		return (List<CalendarDtl>)query.list();
	}
	
	public CalendarDtl getCalendarDtl(String catId,Date date)throws Exception{
		Criteria criteria = getSession().createCriteria(CalendarDtl.class);
		criteria.add(Restrictions.eq("calCat.calendarCatId", catId));
		criteria.add(Restrictions.eq("type", PartnerConstant.PARTNER_CAL_TYPE_DATE));
		Date fromDate = DateUtil.formatDateByTime(date, 0, 0, 0, 0);
		Date toDate = DateUtil.formatDateByTime(date, 23, 59, 59, 0);
		criteria.add(Restrictions.ge("calDate", fromDate));
		criteria.add(Restrictions.le("calDate", toDate));
		criteria.add(Restrictions.eq("isDeleted", CommonConstant.NOT_DELETED));
		CalendarDtl calendarDtl = null;
		List<CalendarDtl> list=criteria.list();
		if (list.size()>0)
			calendarDtl = list.get(0);
		return calendarDtl;
	}
	
	public List<CalendarDtl> getCalendarDtlListByName(String name,String day,Date date,String catId)throws Exception{
		StringBuffer str = new StringBuffer();
		str.append(" select cat from CalendarDtl cat where cat.isDeleted=:value and cat.calCat.calendarCatId=:catId ");
		str.append(" and ( cat.name=:name  ");
		if(date!=null){	 
			str.append(" or ( cat.calDate=:date  )");
		}
		if(day!=null){
			
			str.append(" or cat.day=:day  ");
		}
		str.append(" )  ");
		
		Query query = getSession().createQuery(str.toString());
		query.setParameter("name", name);
		query.setParameter("value", CommonConstant.NOT_DELETED);
		query.setParameter("catId", catId);
		if(date!=null){
			query.setParameter("date", date);
		}
		if(day!=null){
			query.setParameter("day", day);
		}
		return (List<CalendarDtl>)query.list();
	}
	
	public List<PartnerProduct> getPartnerProductList(String partnerId)throws Exception{
		StringBuffer str = new StringBuffer();
		str.append(" select prod from PartnerProduct prod where prod.isDeleted=:value  ");
		str.append(" and prod.partner.partnerId=:partnerId ");
		
		Query query = getSession().createQuery(str.toString());
		query.setParameter("partnerId", partnerId);
		query.setParameter("value", CommonConstant.NOT_DELETED);
		return (List<PartnerProduct>)query.list();
	}
	
	public List<PartnerProduct> getPartnerProductListWithName(String partnerId,String name)throws Exception{
		StringBuffer str = new StringBuffer();
		str.append(" select prod from PartnerProduct prod where prod.isDeleted=:value  ");
		str.append(" and prod.partner.partnerId=:partnerId ");
		str.append(" and prod.name=:name ");
		Query query = getSession().createQuery(str.toString());
		query.setParameter("partnerId", partnerId);
		query.setParameter("name", name);
		query.setParameter("value", CommonConstant.NOT_DELETED);
		return (List<PartnerProduct>)query.list();
	}
	
	public List<PartnerProduct> getPartnerProductListWithCode(String code)throws Exception{
		StringBuffer str = new StringBuffer();
		str.append(" select prod from PartnerProduct prod where prod.isDeleted=:value  ");
		str.append(" and prod.code=:code ");
		Query query = getSession().createQuery(str.toString());
		query.setParameter("code", code);
		query.setParameter("value", CommonConstant.NOT_DELETED);
		return (List<PartnerProduct>)query.list();
	} 
	public Member getMemberByUserId(String userId)throws Exception{
		StringBuffer strb = new StringBuffer();
		strb.append(" select me from Member me where me.loginUser.userId=:userId");
		
		Query query = getSession().createQuery(strb.toString());
		query.setParameter("userId", userId);
		
		return (Member)query.uniqueResult();
	}
	
	public List<PartnerProductBooking> getPartnerProductBookingList(Date from,Date to,String partnerId)throws Exception{
		StringBuffer strb = new StringBuffer();
		strb.append(" select booking from PartnerProductBooking booking where booking.isDeleted=:value");
		strb.append(" and booking.partner.partnerId=:partnerId ");
		if(from!=null&&to!=null){
			strb.append(" and booking.from>=:from and booking.to<=:to ");
		}
		Query query = getSession().createQuery(strb.toString());
		query.setParameter("value", CommonConstant.NOT_DELETED);
		query.setParameter("partnerId", partnerId);
		
		if(from!=null&&to!=null){
			query.setParameter("from", from);
			query.setParameter("to", to);
		}
		
		return (List<PartnerProductBooking>)query.list();
		
	}
	
	public List<PartnerProductExtension> getPartnerProductExtensionList(String productId)throws Exception{
		StringBuffer str = new StringBuffer();
		str.append(" select ext from PartnerProductExtension ext where ext.isDeleted=:value");
		str.append(" and ext.partnerProduct.partnerProductId=:productId ");
		
		Query query = getSession().createQuery(str.toString());
		query.setParameter("value", CommonConstant.NOT_DELETED);
		query.setParameter("productId", productId);
		
		return (List<PartnerProductExtension>)query.list();
	}
	
	//Added by Jacky 16-03-2008 - 1057
	public List<PartnerProduct> getPartnerProductApprovedList()throws Exception{
		Criteria criteria = getSession().createCriteria(PartnerProduct.class);
		criteria.add(Restrictions.eq("status", PartnerConstant.PARTNER_PRODUCT_STATUS_APPROVED));
		List<PartnerProduct> list=criteria.list();
		return list;
	}
	//Added by jacky 17-11-2008 - 1518
	public List<PartnerProduct> getPartnerProductApprovedList(String partnerId)throws Exception{
		Criteria criteria = getSession().createCriteria(PartnerProduct.class);
		criteria.add(Restrictions.eq("status", PartnerConstant.PARTNER_PRODUCT_STATUS_APPROVED));
		criteria.add(Restrictions.eq("partner.partnerId", partnerId));
		
		List<PartnerProduct> list=criteria.list();
		return list;
	}
}
